/*
select * from fn_yield_cost_report() where lotNumber in ( 'NTL01.00', 'TTXHL.01' )

select distinct part_number from product where p_marketing_part_number is not null
select processStep_id from product where part_number = 'TEST-TX-12' and p_marketing_part_number is not null order by processStep_id desc
select * from fn_get_process_flow_backward_by_id(1439) order by temp_count desc
select * from fn_yielded_cost_report() where processStepID = 1433

select * from fn_get_process_flow_by_id(1439)
*/

alter function fn_yield_cost_report()

returns  @fn_result Table( processStepID int, LotNumber varchar(50), ProductName varchar(50), DateIn varchar(30), 
				DateOut varchar(30), QtyIn int, QtyOut int, Step_Yield numeric(9,2), Total_Yield numeric(9,2), 
				std_unit_cost numeric(9,2), std_cum_unit_cost numeric(9,2), std_finished_unit_cost numeric(9,2), 
				vnd_unit_cost numeric(9,2), vnd_cum_unit_cost numeric(9,2), vnd_finished_unit_cost numeric(9,2), 
				Stage int, VendorLoc varchar(50) )
AS
BEGIN
	declare @qty_in int
	declare @qty_out int
	declare @lot_id int
	declare @lot_no varchar(50)
	declare @location varchar(50)
	declare @lot_cost numeric(9,2)
	declare @yield float	
	declare @total_yield float	
	declare @gross_die int
	declare @date_in datetime
	declare @date_out datetime
	declare @ps_step_id int
	declare @ps_type_name varchar(50)
	declare @prod_name varchar(50)
	declare @stage int
	declare @quarter varchar(50)
	declare @year varchar(4)

 	declare @std_wafer_cost float		-- Standard Wafer Cost
 	declare @vnd_wafer_cost float		-- Vendor Wafer Cost	
 	declare @std_unit_cost float		-- Standard Unit Die Cost
 	declare @vnd_unit_cost float		-- Vendor Unit Die Cost
 	declare @std_cum_unit_cost float	-- Standard Cummilative Unit Cost 
 	declare @vnd_cum_unit_cost float	-- Vendor Cummulative Unit Cost
	declare @gross_die_per_wafer int
	declare @net_die_per_wafer int
	declare @location_id int
	declare @pre_lot_no varchar(20)
	declare @std_cum_cost float
	declare @vnd_cum_cost float
	declare @total_wafers_out int
	declare @in_units_per_wafer float
	declare @units_per_wafer float

	declare @std_fab_step_cost float
	declare @vnd_fab_step_cost float
	declare @std_sort_step_cost float
	declare @vnd_sort_step_cost float
	declare @std_assy_step_cost float
	declare @vnd_assy_step_cost float
	declare @std_test_step_cost float
	declare @vnd_test_step_cost float

	declare @std_finished_cost_in_fab float
	declare @std_finished_cost_in_sort float
	declare @std_finished_cost_in_assy float
	declare @std_finished_cost_in_test float
	declare @vnd_finished_cost_in_fab float
	declare @vnd_finished_cost_in_sort float
	declare @vnd_finished_cost_in_assy float
	declare @vnd_finished_cost_in_test float

	declare @results Table( processStepID int, LotNumber varchar(50), ProductName varchar(50), DateIn varchar(30), 
				DateOut varchar(30), QtyIn int, QtyOut int, Step_Yield float, Total_Yield float, 
				std_unit_cost numeric(9,2), std_cum_unit_cost numeric(9,2), std_finished_unit_cost numeric(9,2), 
				vnd_unit_cost numeric(9,2), vnd_cum_unit_cost numeric(9,2), vnd_finished_unit_cost numeric(9,2), 
				Stage int, VendorLoc varchar(50) )
	
	declare lot_cursor cursor for
	select * from fn_all_locations_yield() where QtyOut <> 0 order by lotnumber, processStepid
	--select * from fn_all_locations_yield() where ProcessStepName = 'FAB' order by lotnumber, processStepid

	open lot_cursor
	fetch next from lot_cursor into @ps_type_name, @ps_step_id, @lot_id, @lot_no, @prod_name, 
			@date_in, @date_out, @qty_in, @qty_out, @yield, @gross_die, @stage, @location

		while @@fetch_status = 0
		begin
			-- Reset all loop variables for the new lot number
			if @lot_no <> @pre_lot_no
			begin
				set @vnd_cum_cost = 0.0
				set @std_cum_cost = 0.0
				set @total_yield = 0.0
				set @pre_lot_no = @lot_no 
	
				set @std_fab_step_cost  = 0.0
				set @vnd_fab_step_cost  = 0.0

				set @std_sort_step_cost = 0.0
				set @vnd_sort_step_cost = 0.0

				set @std_assy_step_cost = 0.0
				set @vnd_assy_step_cost = 0.0

				set @std_test_step_cost = 0.0
				set @vnd_test_step_cost = 0.0

				set @std_finished_cost_in_fab  = 0.0
				set @vnd_finished_cost_in_fab  = 0.0

				set @std_finished_cost_in_sort = 0.0
				set @vnd_finished_cost_in_sort = 0.0

				set @std_finished_cost_in_assy = 0.0
				set @vnd_finished_cost_in_assy = 0.0

				set @std_finished_cost_in_test = 0.0
				set @vnd_finished_cost_in_test = 0.0
			end
			
			select @location_id = location_id, @lot_cost = Lot_Cost from lot_table where lot_id = @lot_id

			if @ps_type_name = 'FAB'
			begin
				select @total_yield = @yield
				select @total_wafers_out = @qty_out
				
				-- We are fetching the Original Lot_Cost
				-- select @vnd_wafer_cost = PSLT_COST_NORMAL from process_step_location_table where ProcessStep_ID = @ps_step_id and Location_ID = @location_id
				set @vnd_wafer_cost = @lot_cost

				-- Here we are getting the Cost for Wafer
				-- select @quarter = CQC_Quarter_ID, @year =  from Company_Quarterly_calendar where CQC_Start_Date >= @date_in order by CQC_End_Date ASC
				select @quarter = CQC_Quarter, @year = CC_Year from Company_calendar, Company_Quarterly_calendar where  Company_calendar.CC_YearID = Company_Quarterly_calendar.CQC_YearID and CQC_Start_Date >= @date_in order by CQC_End_Date ASC 
			
				if @quarter = 'Quarter 1' or @quarter = 'Quarter1'
				begin	
					select @std_wafer_cost = Q1_Standard_Cost from Update_View_Cost_Table where ProcessStep_ID = @ps_step_id and Location_ID = @location_id and DATEPART ( yyyy , Fin_Year ) = @year
				end
				else if @quarter = 'Quarter 2' or @quarter = 'Quarter2'
				begin
					select @std_wafer_cost = Q2_Standard_Cost from Update_View_Cost_Table where ProcessStep_ID = @ps_step_id and Location_ID = @location_id and DATEPART ( yyyy , Fin_Year ) = @year
				end
				else if @quarter = 'Quarter 3' or @quarter = 'Quarter3'
				begin
					select @std_wafer_cost = Q3_Standard_Cost from Update_View_Cost_Table where ProcessStep_ID = @ps_step_id and Location_ID = @location_id and DATEPART ( yyyy , Fin_Year ) = @year
				end
				else if  @quarter = 'Quarter 4' or @quarter = 'Quarter4'
				begin
					select @std_wafer_cost = Q4_Standard_Cost from Update_View_Cost_Table where ProcessStep_ID = @ps_step_id and Location_ID = @location_id and DATEPART ( yyyy , Fin_Year ) = @year
				end

				if @std_wafer_cost is null
				begin
					--select @std_wafer_cost = Parameter_Value from Process_Parameter where processStep_ID = @ps_step_id and Parameter_Key = 'Fab Cost'
					select @std_wafer_cost = PSLT_COST_NORMAL from process_step_location_table where ProcessStep_ID = @ps_step_id and Location_ID = @location_id
				end

				select @gross_die_per_wafer = Parameter_Value from Process_Parameter where processStep_ID = @ps_step_id and Parameter_Key = 'Gross Die/Wafer'

				if @gross_die_per_wafer is not null and @gross_die_per_wafer <> 0
				begin
					select @std_fab_step_cost = @std_wafer_cost
					select @vnd_fab_step_cost = @vnd_wafer_cost

					select @vnd_cum_unit_cost = @vnd_wafer_cost / @gross_die_per_wafer
					select @std_cum_unit_cost = @std_wafer_cost / @gross_die_per_wafer
				end
	
				set @vnd_cum_cost = @vnd_wafer_cost
				set @std_cum_cost = @std_wafer_cost

				-- Calculate Finished Cost by Component				
				select @std_finished_cost_in_fab  = (@std_fab_step_cost * @qty_in) / (@gross_die_per_wafer * @total_wafers_out)
				select @vnd_finished_cost_in_fab  = (@vnd_fab_step_cost * @qty_in) / (@gross_die_per_wafer * @total_wafers_out)

				insert into @results values( @ps_step_id, @lot_no, @prod_name, convert(varchar, @date_in, 101), convert(varchar, @date_out, 101), 
							     @qty_in, @qty_out, @yield, @total_yield, @std_cum_unit_cost, @std_cum_unit_cost, @std_finished_cost_in_fab,
							     @vnd_cum_unit_cost, @vnd_cum_unit_cost, @vnd_finished_cost_in_fab, 1, @location )
				
			end

			if @ps_type_name = 'SORT'
			begin
				select @total_yield = (@total_yield*@yield)/100
				select @units_per_wafer = (@qty_out/@total_wafers_out)

				set @vnd_wafer_cost = @lot_cost
				-- select @vnd_wafer_cost = PSLT_COST_NORMAL from process_step_location_table where ProcessStep_ID = @ps_step_id and Location_ID = @location_id
				
				-- select @std_wafer_cost = Parameter_value from process_parameter where processStep_ID = @ps_step_id and Parameter_key = 'Sort Cost'
				select @quarter = CQC_Quarter, @year = CC_Year from Company_calendar, Company_Quarterly_calendar where  Company_calendar.CC_YearID = Company_Quarterly_calendar.CQC_YearID and CQC_Start_Date >= @date_in order by CQC_End_Date ASC
			
				if @quarter = 'Quarter 1' or @quarter = 'Quarter1'
				begin	
					select @std_wafer_cost = Q1_Standard_Cost from Update_View_Cost_Table where ProcessStep_ID = @ps_step_id and Location_ID = @location_id and DATEPART ( yyyy , Fin_Year ) = @year
				end
				else if @quarter = 'Quarter 2' or @quarter = 'Quarter2'
				begin
					select @std_wafer_cost = Q2_Standard_Cost from Update_View_Cost_Table where ProcessStep_ID = @ps_step_id and Location_ID = @location_id and DATEPART ( yyyy , Fin_Year ) = @year
				end
				else if @quarter = 'Quarter 3' or @quarter = 'Quarter3'
				begin
					select @std_wafer_cost = Q3_Standard_Cost from Update_View_Cost_Table where ProcessStep_ID = @ps_step_id and Location_ID = @location_id and DATEPART ( yyyy , Fin_Year ) = @year
				end
				else if  @quarter = 'Quarter 4' or @quarter = 'Quarter4'
				begin
					select @std_wafer_cost = Q4_Standard_Cost from Update_View_Cost_Table where ProcessStep_ID = @ps_step_id and Location_ID = @location_id and DATEPART ( yyyy , Fin_Year ) = @year
				end

				if @std_wafer_cost is null
				begin
					--select @std_wafer_cost = Parameter_value from process_parameter where processStep_ID = @ps_step_id and Parameter_key = 'Sort Cost'
					select @std_wafer_cost = PSLT_COST_NORMAL from process_step_location_table where ProcessStep_ID = @ps_step_id and Location_ID = @location_id
				end

				select @std_sort_step_cost = @std_wafer_cost
				select @vnd_sort_step_cost = @vnd_wafer_cost

				select @vnd_cum_cost = (@vnd_cum_cost + @vnd_sort_step_cost)
				select @std_cum_cost = (@std_cum_cost + @std_sort_step_cost)

				select @vnd_cum_unit_cost = @vnd_cum_cost / @units_per_wafer
				select @std_cum_unit_cost = @std_cum_cost / @units_per_wafer

				-- Calculate Finished Cost by Component				
				select @std_finished_cost_in_fab  = @std_fab_step_cost / @units_per_wafer
				select @vnd_finished_cost_in_fab  = @vnd_fab_step_cost / @units_per_wafer

				select @std_finished_cost_in_sort = @std_sort_step_cost / @units_per_wafer
				select @vnd_finished_cost_in_sort = @vnd_sort_step_cost / @units_per_wafer

				insert into @results values( @ps_step_id, @lot_no, @prod_name, convert(varchar, @date_in, 101), convert(varchar, @date_out, 101), 
							     @qty_in, @qty_out, @yield, @total_yield, @std_wafer_cost/@gross_die_per_wafer, @std_cum_unit_cost, @std_finished_cost_in_sort, 
							     @vnd_wafer_cost/@gross_die_per_wafer, @vnd_cum_unit_cost, @vnd_finished_cost_in_sort, 2, @location )
				
				update @results set std_finished_unit_cost = @std_finished_cost_in_fab, vnd_finished_unit_cost = @vnd_finished_cost_in_fab where lotNumber = @lot_no and stage = 1

			end
			
			if @ps_type_name = 'ASSEMBLY'
			begin
				select @total_yield = (@total_yield*@yield)/100
				select @units_per_wafer = (@qty_out/@total_wafers_out)

				--select @vnd_unit_cost = PSLT_COST_NORMAL from process_step_location_table where ProcessStep_ID = @ps_step_id and Location_ID = @location_id				
				set @vnd_unit_cost = @lot_cost

				--select @std_unit_cost = Parameter_value from process_parameter where processStep_ID = @ps_step_id and Parameter_key = 'Assembly Cost'
				select @quarter = CQC_Quarter, @year = CC_Year from Company_calendar, Company_Quarterly_calendar where  Company_calendar.CC_YearID = Company_Quarterly_calendar.CQC_YearID and CQC_Start_Date >= @date_in order by CQC_End_Date ASC
			
				if @quarter = 'Quarter 1' or @quarter = 'Quarter1'
				begin	
					select @std_unit_cost = Q1_Standard_Cost from Update_View_Cost_Table where ProcessStep_ID = @ps_step_id and Location_ID = @location_id and DATEPART ( yyyy , Fin_Year ) = @year
				end
				else if @quarter = 'Quarter 2' or @quarter = 'Quarter2'
				begin
					select @std_unit_cost = Q2_Standard_Cost from Update_View_Cost_Table where ProcessStep_ID = @ps_step_id and Location_ID = @location_id and DATEPART ( yyyy , Fin_Year ) = @year
				end
				else if @quarter = 'Quarter 3' or @quarter = 'Quarter3'
				begin
					select @std_unit_cost = Q3_Standard_Cost from Update_View_Cost_Table where ProcessStep_ID = @ps_step_id and Location_ID = @location_id and DATEPART ( yyyy , Fin_Year ) = @year
				end
				else if  @quarter = 'Quarter 4' or @quarter = 'Quarter4'
				begin
					select @std_unit_cost = Q4_Standard_Cost from Update_View_Cost_Table where ProcessStep_ID = @ps_step_id and Location_ID = @location_id and DATEPART ( yyyy , Fin_Year ) = @year
				end

				if @std_unit_cost is null
				begin
					--select @std_unit_cost = Parameter_value from process_parameter where processStep_ID = @ps_step_id and Parameter_key = 'Assembly Cost'
					select @std_unit_cost = PSLT_COST_NORMAL from process_step_location_table where ProcessStep_ID = @ps_step_id and Location_ID = @location_id
				end

				select @std_assy_step_cost = ( @units_per_wafer * @std_unit_cost )
				select @vnd_assy_step_cost = ( @units_per_wafer * @vnd_unit_cost )

				select @vnd_cum_cost = ( @vnd_cum_cost + @vnd_assy_step_cost )
				select @std_cum_cost = ( @std_cum_cost + @std_assy_step_cost )

				select @vnd_cum_unit_cost = @vnd_cum_cost/@units_per_wafer
				select @std_cum_unit_cost = @std_cum_cost/@units_per_wafer


				-- Calculate Finished Cost by Component				
				select @std_finished_cost_in_fab  = @std_fab_step_cost / @units_per_wafer
				select @vnd_finished_cost_in_fab  = @vnd_fab_step_cost / @units_per_wafer

				select @std_finished_cost_in_sort = @std_sort_step_cost / @units_per_wafer
				select @vnd_finished_cost_in_sort = @vnd_sort_step_cost / @units_per_wafer

				select @std_finished_cost_in_assy = @std_assy_step_cost / @units_per_wafer
				select @vnd_finished_cost_in_assy = @vnd_assy_step_cost / @units_per_wafer


				insert into @results values( @ps_step_id, @lot_no, @prod_name, convert(varchar, @date_in, 101), convert(varchar, @date_out, 101), 
							     @qty_in, @qty_out, @yield, @total_yield, @std_unit_cost, @std_cum_unit_cost, @std_finished_cost_in_assy, 
							     @vnd_unit_cost, @vnd_cum_unit_cost, @vnd_finished_cost_in_assy, 3, @location )
				
				update @results set std_finished_unit_cost = @std_finished_cost_in_fab, vnd_finished_unit_cost = @vnd_finished_cost_in_fab where lotNumber = @lot_no and stage = 1
				update @results set std_finished_unit_cost = @std_finished_cost_in_sort, vnd_finished_unit_cost = @vnd_finished_cost_in_sort where lotNumber = @lot_no and stage = 2

			end

			if @ps_type_name = 'TEST'
			begin
				select @total_yield = (@total_yield*@yield)/100
				select @in_units_per_wafer = (@qty_in/@total_wafers_out)
				select @units_per_wafer = (@qty_out/@total_wafers_out)

				--select @vnd_unit_cost = PSLT_COST_NORMAL from process_step_location_table where ProcessStep_ID = @ps_step_id and Location_ID = @location_id
				set @std_unit_cost = @lot_cost

				--select @std_unit_cost = Parameter_value from process_parameter where processStep_ID = @ps_step_id and Parameter_key = 'Test Cost'
				select @quarter = CQC_Quarter, @year = CC_Year from Company_calendar, Company_Quarterly_calendar where  Company_calendar.CC_YearID = Company_Quarterly_calendar.CQC_YearID and CQC_Start_Date >= @date_in order by CQC_End_Date ASC
			
				if @quarter = 'Quarter 1' or @quarter = 'Quarter1'
				begin	
					select @std_unit_cost = Q1_Standard_Cost from Update_View_Cost_Table where ProcessStep_ID = @ps_step_id and Location_ID = @location_id and DATEPART ( yyyy , Fin_Year ) = @year
				end
				else if @quarter = 'Quarter 2' or @quarter = 'Quarter2'
				begin
					select @std_unit_cost = Q2_Standard_Cost from Update_View_Cost_Table where ProcessStep_ID = @ps_step_id and Location_ID = @location_id and DATEPART ( yyyy , Fin_Year ) = @year
				end
				else if @quarter = 'Quarter 3' or @quarter = 'Quarter3'
				begin
					select @std_unit_cost = Q3_Standard_Cost from Update_View_Cost_Table where ProcessStep_ID = @ps_step_id and Location_ID = @location_id and DATEPART ( yyyy , Fin_Year ) = @year
				end
				else if  @quarter = 'Quarter 4' or @quarter = 'Quarter4'
				begin
					select @std_unit_cost = Q4_Standard_Cost from Update_View_Cost_Table where ProcessStep_ID = @ps_step_id and Location_ID = @location_id and DATEPART ( yyyy , Fin_Year ) = @year
				end


				if @std_unit_cost is null
				begin
					--select @std_unit_cost = Parameter_value from process_parameter where processStep_ID = @ps_step_id and Parameter_key = 'Test Cost'
					select @std_unit_cost = PSLT_COST_NORMAL from process_step_location_table where ProcessStep_ID = @ps_step_id and Location_ID = @location_id
				end

				select @std_test_step_cost = ( @in_units_per_wafer * @std_unit_cost )
				select @vnd_test_step_cost = ( @in_units_per_wafer * @vnd_unit_cost )

				select @vnd_cum_cost = ( @vnd_cum_cost + @vnd_test_step_cost )
				select @std_cum_cost = ( @std_cum_cost + @std_test_step_cost )

				select @vnd_cum_unit_cost = @vnd_cum_cost/@units_per_wafer
				select @std_cum_unit_cost = @std_cum_cost/@units_per_wafer

				-- Calculate Finished Cost by Component				
				select @std_finished_cost_in_fab  = @std_fab_step_cost / @units_per_wafer
				select @vnd_finished_cost_in_fab  = @vnd_fab_step_cost / @units_per_wafer

				select @std_finished_cost_in_sort = @std_sort_step_cost / @units_per_wafer
				select @vnd_finished_cost_in_sort = @vnd_sort_step_cost / @units_per_wafer

				select @std_finished_cost_in_assy = @std_assy_step_cost / @units_per_wafer
				select @vnd_finished_cost_in_assy = @vnd_assy_step_cost / @units_per_wafer

				select @std_finished_cost_in_test = @std_test_step_cost / @units_per_wafer
				select @vnd_finished_cost_in_test = @vnd_test_step_cost / @units_per_wafer

				insert into @results values( @ps_step_id, @lot_no, @prod_name, convert(varchar, @date_in, 101), convert(varchar, @date_out, 101), 
							     @qty_in, @qty_out, @yield, @total_yield, @std_unit_cost, @std_cum_unit_cost, @std_finished_cost_in_test, 
							     @vnd_unit_cost, @vnd_cum_unit_cost, @vnd_finished_cost_in_test, 4, @location )
				
				update @results set std_finished_unit_cost = @std_finished_cost_in_fab, vnd_finished_unit_cost = @vnd_finished_cost_in_fab where lotNumber = @lot_no and stage = 1
				update @results set std_finished_unit_cost = @std_finished_cost_in_sort, vnd_finished_unit_cost = @vnd_finished_cost_in_sort where lotNumber = @lot_no and stage = 2
				update @results set std_finished_unit_cost = @std_finished_cost_in_assy, vnd_finished_unit_cost = @vnd_finished_cost_in_assy where lotNumber = @lot_no and stage = 3

			end

			set @date_in = null
			set @date_out = null
			set @qty_in = null
			set @qty_out = null
			set @yield = null
			set @gross_die = null

			set @std_wafer_cost = null
			set @vnd_wafer_cost = null
			set @std_unit_cost = null		
			set @vnd_unit_cost = null
			set @std_cum_unit_cost = null		
			set @vnd_cum_unit_cost = null
			--set @gross_die_per_wafer = null
			set @net_die_per_wafer = null

			fetch next from lot_cursor into @ps_type_name, @ps_step_id, @lot_id, @lot_no, @prod_name, 
			@date_in, @date_out, @qty_in, @qty_out, @yield, @gross_die, @stage, @location
		end

	close lot_cursor
	deallocate lot_cursor

	insert @fn_result select * from @results 
	return

end